//201902104003邵潇漩
package bysj.dao;

import bysj.domain.GraduateProjectType;
import util.JdbcHelper;
import java.sql.*;
import java.util.Collection;
import java.util.TreeSet;

public final class GraduateProjectTypeDao {
    private static GraduateProjectTypeDao graduateProjectTypeDao = null;
    private GraduateProjectTypeDao() {
    }

    public static GraduateProjectTypeDao getInstance() {
        // 静态变量schoolDao为空时，才创建SchoolDao对象。
        // 第二次调用本方法schoolDao就不空了，不再创建对象。
        if (graduateProjectTypeDao == null){
            graduateProjectTypeDao = new GraduateProjectTypeDao();
        }
        return graduateProjectTypeDao;
    }

    /*public static void main(String[] args) {
        GraduateProjectTypeDao dao = new GraduateProjectTypeDao();
        Collection<GraduateProjectType> graduateProjectTypes = dao.finaAll();
        display(graduateProjectTypes);
    }

    private static void display(Collection<GraduateProjectType> graduateProjectTypes) {
        for (GraduateProjectType graduateProjectType : graduateProjectTypes) {
            System.out.println(graduateProjectType);
        }
    }*/

    public static Collection<GraduateProjectType> findAll() throws SQLException {
        Collection<GraduateProjectType> graduateProjectTypes = new TreeSet<GraduateProjectType>();
        //获得连接对象
        Connection conn= JdbcHelper.getConn();
        //创建sql语句
        String selectGraduateProjectType_sql="select * from graduateProjectType";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt=conn.prepareStatement(selectGraduateProjectType_sql);
        pstmt.execute();
        //执行SQL语句并获得结果集对象
        ResultSet resultSet=pstmt.executeQuery();
        //若结果集仍然有下一条记录，则执行循环体
        while (resultSet.next()) {
            //创建School对象
            GraduateProjectType graduateProjectType=new GraduateProjectType(null,"","","");
            //给school对象的字段赋值
            graduateProjectType.setId(resultSet.getInt("id"));
            graduateProjectType.setDescription(resultSet.getString("description"));
            graduateProjectType.setNo(resultSet.getString("no"));
            graduateProjectType.setRemarks(resultSet.getString("remarks"));
            graduateProjectTypes.add(graduateProjectType);
        }
        //关闭连接
        JdbcHelper.close(resultSet,pstmt,conn);
        return graduateProjectTypes;
    }

    public GraduateProjectType find(Integer id,Connection conn) throws SQLException {
        String selectGraduateProjectType_sql="select * from graduateProjectType WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt=conn.prepareStatement(selectGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //执行SQL语句并获得结果集对象
        ResultSet resultSet=pstmt.executeQuery();
        GraduateProjectType graduateProjectType=new GraduateProjectType(null,"","","");
        //若结果集仍然有下一条记录，则执行循环体
        if (resultSet.next()) {
            //给graduateProjectType对象的字段赋值
            graduateProjectType.setId(resultSet.getInt("id"));
            graduateProjectType.setDescription(resultSet.getString("description"));
            graduateProjectType.setNo(resultSet.getString("no"));
            graduateProjectType.setRemarks(resultSet.getString("remarks"));
        }
        //关闭连接
        JdbcHelper.close(resultSet,pstmt);
        //返回school对象
        return graduateProjectType;
    }

    public boolean update(GraduateProjectType graduateProjectType) throws SQLException {
        //获得连接对象
        Connection conn=JdbcHelper.getConn();
        //创建sql语句
        String updateGraduateProjectType_sql = "UPDATE graduateProjectType SET description=?, no=?, remarks=? WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProjectType.getDescription());
        pstmt.setString(2,graduateProjectType.getNo());
        pstmt.setString(3,graduateProjectType.getRemarks());
        pstmt.setInt(4,graduateProjectType.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt,conn);
        //返回school对象是否修改成功
        return affectedRowNum>0;
    }

    public boolean add(GraduateProjectType graduateProjectType) throws SQLException {
        //获得连接对象
        Connection conn=JdbcHelper.getConn();
        //根据连接对象准备调用语句对象，sp_addSchool为存储过程名称，后面为4个参数
        CallableStatement callableStatement=conn.prepareCall("{CALL sp_addGraduateProjectType(?,?,?,?)}");
        //将第4个参数设置为输出参数，类型为长整型
        callableStatement.registerOutParameter(4,Types.BIGINT);
        callableStatement.setString(1,graduateProjectType.getDescription());
        callableStatement.setString(2,graduateProjectType.getNo());
        callableStatement.setString(3,graduateProjectType.getRemarks());
        //执行可调用语句
        int affectedRowNum = callableStatement.executeUpdate();
        //获取第5个参数的值，数据库为该记录自动生成的id
        int id=callableStatement.getInt(4);
        //为参数school的id字段赋值
        graduateProjectType.setId(id);
        callableStatement.close();
        conn.close();
        return affectedRowNum>0;
    }

    public boolean delete(Integer id) throws SQLException {
        //获得连接对象
        Connection conn=JdbcHelper.getConn();
        //创建sql语句
        String deleteGraduateProjectType_sql = "DELETE FROM graduateProjectType WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt,conn);
        //如果删除记录的行数不为0，返回true,否则返回false
        return affectedRowNum>0;
    }
}
